package com.server.dataSource;

import com.dao.dataSource.DataColumnMapper;
import com.dao.dataSource.DataConnectorMapper;
import com.model.bo.ToSql;
import com.model.po.Databases;
import com.model.pojo.RepCode;
import com.model.pojo.RepEntity;
import com.model.vo.dataVo.ColumnTypeInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.sql.*;
import java.util.*;
import java.util.Date;

@Service
public class ImplementSqlService {
    @Autowired
    DataColumnMapper dataColumnMapper;
    @Autowired
    DataConnectorMapper dataConnectorMapper;

    /*
    执行数据源
     */
    public RepEntity implementSql(ToSql toSql) {
        List<String> checkSql = java.util.Arrays.asList(toSql.getStrSql().toLowerCase().split(" "));
        if (checkSql.contains("update") || checkSql.contains("delete") || checkSql.contains("insert") ||
                checkSql.contains("drop") || checkSql.contains("create") || checkSql.contains("comment")){
            return new RepEntity(RepCode.SqlWarn);
        }
        String sqlStr = "select * from (" + toSql.getStrSql() + ") where rownum <=1";
        System.out.println(sqlStr);
        if ("".equals(sqlStr) || sqlStr == null) {
            return new RepEntity(RepCode.ChartsNameNull);
        }

        LinkedHashMap<String, Object> columnData = dataColumnMapper.getColumn(sqlStr);
        LinkedHashMap<String, String> tarValue = getColumnType(columnData);

        List<ColumnTypeInfo> isList = new ArrayList<>();

        Iterator<String> iter = columnData.keySet().iterator();
        while (iter.hasNext()){
            String key = iter.next();
            String vaul = tarValue.get(key);
            ColumnTypeInfo columnTypeInfo = new ColumnTypeInfo();
            columnTypeInfo.setColumnName(key);
            columnTypeInfo.setColumnType(vaul);
            isList.add(columnTypeInfo);
        }

        return new RepEntity(RepCode.success,isList);
    }

     /*
    执行数据源test
     */
//     public RepEntity getColumnTest(ToSql toSql){
//         String sqlStr = toSql.getStrSql();
//         Connection conn = null;
//         try {
//             String url = "jdbc:oracle:thin:@218.17.158.219:1521/orcl";
//             String user = "UAS";
//             String pass = "select!#%*(";
//             conn = DriverManager.getConnection(url,user,pass);
//             String sql="select as_id from accidinsur";
//             PreparedStatement stmt;
//             stmt = conn.prepareStatement(sql);
//             System.out.println("...........");
//             ResultSet rs = stmt.executeQuery(sql);
//             System.out.println("1111111111");
//
//             ResultSetMetaData rsmd = rs.getMetaData();
//             String name = rsmd.getCatalogName(1);
//             int col = rsmd.getColumnCount();   //获得列的个数
//             System.out.println("num"+col);
//             System.out.println("name"+name);
//
//             ResultSetMetaData data = rs.getMetaData();
//             while (rs.next()) {
//                 for (int i = 1; i <= data.getColumnCount(); i++) {
//                     //获得所有列的数目及实际列数
//                     int columnCount = data.getColumnCount();
//                     System.out.println("数:"+columnCount);
//                     // 获得指定列的列名
//                     String columnName = data.getColumnName(i);
//                     System.out.println("ming:"+columnName);
//                     //获得指定列的列值
//                     String columnValue = rs.getString(i);
//                     System.out.println("value"+columnValue);
//                     //获得指定列的数据类型
//                     int columnType = data.getColumnType(i);
//                     System.out.println("type"+columnType);
//                 }
//             }
//         }catch (SQLException e){
//
//         }
//                     return new RepEntity(RepCode.success);
//     }

    /*
    判断列类型
     */
    public LinkedHashMap<String, String> getColumnType(LinkedHashMap<String, Object> columnData){
        LinkedHashMap<String, String> tarValue = new LinkedHashMap<>();
            LinkedHashMap<String, Object> columnDataMap = columnData;
            Iterator<String> cm = columnDataMap.keySet().iterator();
            while (cm.hasNext()){
                String key = cm.next();
                if (tarValue.containsKey(key)){
                    continue;
                }else {
                    Object value = columnDataMap.get(key);
                    if (value == null) {
                        continue;
                    }else {
                        String values = getType(value);
                        tarValue.put(key, values);
                    }
                    if (tarValue.size() == columnDataMap.size()){
                        break;
                    }
                }
            }
        return tarValue;
    }

    /*
    判断值类型
     */
    public String getType(Object obj){
        if(obj instanceof String){
            return "String";
        }else if (obj instanceof Double){
            return "Double";
        }else if (obj instanceof Integer){
            return "Integer";
        }else if (obj instanceof Date){
            return "Date";
        }else if (obj instanceof Boolean){
            return "Boolean";
        }else if(obj instanceof Byte){
            return "Byte";
        }else if (obj instanceof Short){
            return "Short";
        }else if (obj instanceof BigDecimal){
            return "BigDecimal";
        }else if (obj instanceof Object){
            return "Object";
        }else {
            return "";
        }
    }



    /*
    判断数据库连接
     */
    public RepEntity connectTest(Databases databases) {
        String password = "";
        String passwords = databases.getPassWord();
        int id = databases.getId();
        String pw = dataConnectorMapper.getBasesById(id);
        if (passwords != null && !"".equals(passwords)){
            password = passwords;
        }else {
            password = pw;
        }

        String url = "jdbc:oracle:thin:@" + databases.getAddrass() + ":" + databases.getPort() + ":" + databases
                .getDataName();
        String username = databases.getUserName();
//        String password = databases.getPassWord();
        Connection con = null;
        try {
            DriverManager.setLoginTimeout(8);
            con = DriverManager.getConnection(url, username, password);
            System.out.println("数据库连接成功");
            con.close();
        } catch (SQLException se) {
            System.out.println("数据库连接失败！");
            se.printStackTrace();
            return new RepEntity(RepCode.Null);
        }
        return new RepEntity(RepCode.success);
    }
}
